-- =============================================
-- Author:		Michael Blumenthal
-- Create date: June 9, 2014
-- Description:	Insert a new customer
-- =============================================
CREATE PROCEDURE CustomerAdd 
	-- Add the parameters for the stored procedure here
@Title nvarchar(8),
@FirstName nvarchar(50),
@LastName nvarchar(50),
@CompanyName nvarchar(128),
@SalesPerson nvarchar(256),
@EmailAddress nvarchar(50),
@Phone nvarchar(25),
@AddressLine1 nvarchar(60),
@City nvarchar(30),
@StateProvince nvarchar(50),
@CountryRegion nvarchar(50),
@PostalCode nvarchar(15)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

DECLARE @NameStyle bit = 0;
DECLARE @CustomerIDTable table( ID int);  
DECLARE @AddressIDTable table( ID int);  
DECLARE @CustomerID int, @AddressID int;

INSERT INTO [SalesLT].[Customer]
           ([NameStyle]
           ,[Title]
           ,[FirstName]
           ,[LastName]
           ,[CompanyName]
           ,[SalesPerson]
           ,[EmailAddress]
           ,[Phone]       
           ,[ModifiedDate])
	 Output inserted.CustomerID into @CustomerIDTable
     VALUES
           (@NameStyle,
           @Title,
           @FirstName,
           @LastName,
           @CompanyName, 
           @SalesPerson, 
           @EmailAddress, 
           @Phone, 
           GETDATE())


INSERT INTO [SalesLT].[Address]
           ([AddressLine1]
           ,[City]
           ,[StateProvince]
           ,[CountryRegion]
           ,[PostalCode]
           ,[ModifiedDate])
	Output inserted.AddressID into @AddressIDTable
     VALUES
           (@AddressLine1,
           @City, 
           @StateProvince,
           @CountryRegion,
           @PostalCode, 
           GETDATE())

SELECT TOP 1 @CustomerID = ID
FROM @CustomerIDTable

SELECT TOP 1 @AddressID = ID
FROM @AddressIDTable

INSERT INTO [SalesLT].[CustomerAddress]
           ([CustomerID]
           ,[AddressID]
           ,[AddressType]
           ,[ModifiedDate])
     VALUES
           (@CustomerID,
           @AddressID, 
           'Main Office',
           GETDATE())

END